package data

import (
	"commerce/common"
	"commerce/model"
	"database/sql"
	"fmt"
	"strings"
)

func PageHelpInfo(pageNo, pageSize int, name string) (*common.Page, error) {

	whereSql := composeQaSearchQuerySql(name)

	row := common.DB.QueryRow("select count(*) FROM help_info" + whereSql)
	var totalRecords int
	if err := row.Scan(&totalRecords); err != nil {
		return nil, err
	}
	stmt, err := common.DB.Prepare("select i.id, i.type_id, i.question, i.answer, i.sort, i.is_valid, s.name FROM help_info i inner join help_issue s on i.type_id = s.id" + whereSql + " LIMIT ?, ?")
	if err != nil {
		return nil, err
	}
	defer stmt.Close()

	rows, err := stmt.Query((pageNo-1)*pageSize, pageSize)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var hs []model.HelpInfo
	for rows.Next() {
		h := model.HelpInfo{}
		if err := rows.Scan(&h.Id, &h.TypeId, &h.Question, &h.Answer, &h.Sort, &h.IsValid, &h.TypeName); err != nil {
			return nil, fmt.Errorf("问答分页数据有误:%s", err.Error())
		}
		hs = append(hs, h)
	}
	return common.NewPage(hs, pageNo, pageSize, totalRecords), nil
}

func GetHelpInfoById(id int) (*model.HelpInfo, error) {

	stmt, err := common.DB.Prepare("select i.id, i.type_id, i.question, i.answer, i.sort, s.name FROM help_info i inner join help_issue s on i.type_id = s.id where i.id = ?")
	if err != nil {
		return nil, err
	}
	defer stmt.Close()

	rows, err := stmt.Query(id)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var h model.HelpInfo
	for rows.Next() {
		if err := rows.Scan(&h.Id, &h.TypeId, &h.Question, &h.Answer, &h.Sort, &h.TypeName); err != nil {
			return nil, fmt.Errorf("help info id查询数据有误:%s", err.Error())
		}
	}
	return &h, nil
}

func AddHelpInfo(h model.HelpInfo) (int, error) {

	stmt, err := common.DB.Prepare(`insert into help_info(type_id, question, answer, sort, is_valid) VALUES (?, ?, ?, ?, ?)`)
	if err != nil {
		return 0, err
	}
	defer stmt.Close()
	result, err := stmt.Exec(h.TypeId, h.Question, h.Answer, h.Sort, h.IsValid)

	if err != nil {
		return 0, fmt.Errorf("save help info err: %v", err)
	}
	id, err := result.LastInsertId()

	if err != nil {
		return 0, fmt.Errorf("save help info insert id err: %v", err)
	}
	return int(id), nil
}

func UpdateHelpInfo(h model.HelpInfo) (int, error) {

	stmt, err := common.DB.Prepare("update help_info set type_id=?, question = ?, answer = ?, sort = ? WHERE id = ?")
	if err != nil {
		return 0, err
	}
	defer stmt.Close()

	var result sql.Result
	result, err = stmt.Exec(h.TypeId, h.Question, h.Answer, h.Sort, h.Id)
	if err != nil {
		return 0, fmt.Errorf("update help info err:%v", err.Error())
	}
	rowsAffected, err := result.RowsAffected()
	return int(rowsAffected), err
}

func UpdateHelpInfoStatus(id int, isValid int8) error {

	stmt, err := common.DB.Prepare("update help_info set is_valid = ? WHERE id = ?")
	if err != nil {
		return err
	}
	defer stmt.Close()

	_, err = stmt.Exec(isValid, id)

	return err
}

func ListHelpInfoByTypeId(typeId int) ([]model.HelpInfo, error) {

	stmt, err := common.DB.Prepare("select id, question, answer FROM help_info where type_id = ? and is_valid = 1 order by sort desc")
	if err != nil {
		return nil, err
	}
	defer stmt.Close()

	rows, err := stmt.Query(typeId)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var hs []model.HelpInfo
	for rows.Next() {
		h := model.HelpInfo{}
		if err := rows.Scan(&h.Id, &h.Question, &h.Answer); err != nil {
			return nil, fmt.Errorf("问答有效数据有误:%s", err.Error())
		}
		hs = append(hs, h)
	}
	return hs, nil
}

func composeQaSearchQuerySql(name string) string {

	// 没有条件查询
	if len(name) == 0 {
		return ""
	}
	sb := strings.Builder{}
	search := "'" + strings.TrimSpace(name) + "%'"
	sb.WriteString(" WHERE (question like " + search + " OR answer like " + search + ") ")

	return sb.String()
}
